﻿using System;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using DBUtility;
using System.Collections;//Please add references
namespace TierApplication
{
    /// <summary>
    /// 类T_PRODUCTIMP。
    /// </summary>
    public partial class T_PRODUCTIMP
    {
        public static DataSet GetProductImpList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT A.IMPBATCHNO, A.IMPDATE, B.USERNAME, A.PRODUCTID, A.PRODUCTNAME,");
            strSql.Append("       (SELECT CODENAME FROM C_IMPSTATUS WHERE CODEID = A.IMPSTATUS) IMPSTATUSNAME,");
            strSql.Append("       (SELECT CODENAME FROM C_BANDWIDTH WHERE CODEID = A.BANDWIDTH) BANDWIDTHNAME,");
            strSql.Append("       (SELECT CODENAME FROM C_LIFETIME WHERE CODEID = A.LIFETIME) LIFETIMENAME,");
            strSql.Append("       (SELECT SCHOOLNAME FROM T_SCHOOL WHERE SCHOOLID = A.SCHOOLID) SCHOOLNAME,");
            strSql.Append("       (SELECT USERNAME FROM P_USER WHERE USERCODE = A.AUDITOR) AUDITORNAME,");
            strSql.Append("       (Select CodeName from c_Categoryid where CodeID=A.Categoryid) CATEGORYNAME,");
            strSql.Append("       A.AUDITDATE,A.PURCHASEPRICE,A.MARKETPRICE,A.DISCOUNT,A.DISCOUNTPRICE,A.UNITPRICE,");
            strSql.Append("       (SELECT COUNT(1) FROM T_PRODUCTITEM WHERE IMPBATCHNO = A.IMPBATCHNO) ITEMCOUNT");
            strSql.Append("  FROM T_PRODUCTIMP A, P_USER B, T_PRODUCT C");
            strSql.Append(" WHERE A.IMPUSERID = B.USERCODE(+)");
            strSql.Append("   AND A.PRODUCTID = C.PRODUCTID(+)" + strWhere);
            return DbHelperOra.Query(strSql.ToString());
        }


        public DataSet GetProductAll(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT A.IMPBATCHNO, A.IMPDATE, B.USERNAME, A.PRODUCTID, A.PRODUCTNAME, A.productimage,");
            strSql.Append("       (SELECT CODENAME FROM C_IMPSTATUS WHERE CODEID = A.IMPSTATUS) IMPSTATUSNAME,");
            strSql.Append("       (SELECT CODENAME FROM C_BANDWIDTH WHERE CODEID = A.BANDWIDTH) BANDWIDTHNAME,");
            strSql.Append("       (SELECT CODENAME FROM C_LIFETIME WHERE CODEID = A.LIFETIME) LIFETIMENAME,");
            strSql.Append("       (SELECT SCHOOLNAME FROM T_SCHOOL WHERE SCHOOLID = A.SCHOOLID) SCHOOLNAME,");
            strSql.Append("       (SELECT USERNAME FROM P_USER WHERE USERCODE = A.AUDITOR) AUDITORNAME,");
            strSql.Append("       (Select CodeName from c_Categoryid where CodeID=A.Categoryid) CATEGORYNAME,");
            strSql.Append("       A.AUDITDATE,A.PURCHASEPRICE,A.MARKETPRICE,A.DISCOUNT,A.DISCOUNTPRICE,A.UNITPRICE,");
            strSql.Append("       (SELECT COUNT(1) FROM T_PRODUCTITEM WHERE IMPBATCHNO = A.IMPBATCHNO) ITEMCOUNT");
            strSql.Append("  FROM T_PRODUCTIMP A, P_USER B, T_PRODUCT C");
            strSql.Append(" WHERE A.IMPUSERID = B.USERCODE(+)");
            strSql.Append("   AND A.PRODUCTID = C.PRODUCTID(+)" + strWhere);
            return DbHelperOra.Query(strSql.ToString());
        }

        public static void DeleteProductItem(string sIMPBATCHNO)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Delete from T_ProductItem where IMPBATCHNO ='" + sIMPBATCHNO + "'");
            DbHelperOra.ExecuteSql(strSql.ToString());
        }

        public void IMP_PRODUCT_IMP_DELETE(string ac_impbatchno, string ac_userid, ref Int32 ai_result, ref string ac_result)
        {
            OracleParameter[] parameters ={
                                               new OracleParameter("ac_impbatchno",OracleType.VarChar,10),
                                 new OracleParameter("ac_userid",OracleType.VarChar,60),
                                  new OracleParameter("ai_result",OracleType.Int32),
                                   new OracleParameter("ac_result",OracleType.VarChar,4000)
                                                };
            parameters[0].Value = ac_impbatchno;
            parameters[1].Value = ac_userid;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.Output;
            try
            {
                OracleCommand cmd = DbHelperOra.RunProcedureReturnCammand("SP_IMP_PRODUCT_IMP_DELETE", parameters);
                ai_result = int.Parse(cmd.Parameters["ai_result"].Value.ToString());
                ac_result = cmd.Parameters["ac_result"].Value.ToString();
            }
            catch (Exception e)
            {
                ai_result = -1;
                ac_result = "调用后台过程错误,错误原因为：" + TierService.PageValidate.RemoveChr10And13(e.Message);
            }


        }


    }
}

